﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_COM_PGM_GRID_COLUMN_Get_Column_List_By_Pgm_Id')
	BEGIN
		DROP Procedure usp_UPDMS_COM_PGM_GRID_COLUMN_Get_Column_List_By_Pgm_Id
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_COM_PGM_GRID_COLUMN_Get_Column_List_By_Pgm_Id
**	Desc : 프로그램별 그리드 컬럼정보 조회
**	Test Exec Query : Exec usp_UPDMS_COM_PGM_GRID_COLUMN_Get_Column_List_By_Pgm_Id 'Crd1012m', 'ko'
**	Called by : All
**	Program ID : All
**	Auth : 송시명
**	Date : 2011-01-12
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**	
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_COM_PGM_GRID_COLUMN_Get_Column_List_By_Pgm_Id]
@ls_pgm_id nvarchar(20),
@ls_lang_set nvarchar(2)
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

SELECT DISTINCT Pgm_Id,
       Grid_Id
  FROM UPDMS_COM_PGM_GRID_COLUMN WITH(NOLOCK)
 WHERE Pgm_Id = @ls_pgm_id


SELECT a.Grid_Id,
       a.Col_Idx,
       ISNULL(a.Col_Nm, '') AS Col_Nm,
       b.Seq AS Label_Id,
       CASE @ls_lang_set WHEN 'ko' THEN ISNULL(b.Label_Ko, '')
                         ELSE ISNULL(b.Label_En,'')
       END Label_Nm
  FROM UPDMS_COM_PGM_GRID_COLUMN AS a WITH(NOLOCK)
  LEFT OUTER JOIN UPDMS_COM_LABEL AS b WITH(NOLOCK) ON a.Label_Id = b.Seq
 WHERE a.Pgm_Id = @ls_pgm_id
 ORDER BY a.Grid_Id, a.Col_Idx

GO